/**
 * This is the service class for rent - return module. 
 * This class connects to the Database and does the necessary operation
 */
package rentReturnService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;

import rentReturnEntity.ReturnEntity;
import constants.Constants;

/**
 * @author Yokesh Kumar
 * 
 */

public class ReturnService {
	private Connection dbConnection;
	private PreparedStatement sqlStatement;
/**
 * Constructor for RentReturn Service 
 * Establishes the database connection
 * @PRE Object for this class is not intantiated
 * @POST Connection to the database is established
 * @throws SQLException
 */
	public ReturnService() throws SQLException {
		try {
			Class.forName(Constants.JDBC_DRIVER);
			dbConnection = DriverManager.getConnection(Constants.DB_URL,
					Constants.USER_NAME, Constants.PWD);
			//System.out.println("DB Connection established");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		}

	}
	
	/**
	 * This method fetches the Fees variables required for calculating Return Bill Cost
	 * @PRE invoked from calculateBill method in ReturnLogic class
	 * @POST Fees values are set in the ArrayList 
	 * @return feesValues
	 * @throws SQLException
	 */
	
	public ArrayList<Double> fetchFeesVariables() throws SQLException{
		System.out.println("in service .. fetching fees");
		ArrayList<Double> feesValues = new ArrayList<Double>();
		ResultSet feesVariables  = null;
		String query = "SELECT * FROM superrentdb.feesVariables WHERE feeName = ? ;";
		try{
			sqlStatement = dbConnection.prepareStatement(query);
			sqlStatement.setString(1, "Service Fee");
			feesVariables = sqlStatement.executeQuery();
			if(feesVariables.next()){
				feesValues.add(feesVariables.getDouble(2));
			}
			sqlStatement.setString(1, "Age Fee");
			feesVariables = sqlStatement.executeQuery();
			if(feesVariables.next()){
				feesValues.add(feesVariables.getDouble(2));
			}
			sqlStatement.setString(1, "Tax Percentage");
			feesVariables = sqlStatement.executeQuery();
			if(feesVariables.next()){
				feesValues.add(feesVariables.getDouble(2));
			}
		}catch (SQLException sqlException) {
			sqlException.printStackTrace();
			throw sqlException;
		}
		return feesValues;
	}
	
	/**
	 * This method connects to the return_vehicle table and retrieves the record for corresponding returnID
	 * @PRE invoked from searchReturn method in ReturnLogic class
	 * @POST Return true if result set is not null , false otherwise
	 * @param returnId
	 * @return boolean
	 * @throws SQLException
	 */
	
	public boolean searchReturn(int returnId) throws SQLException{

		System.out.println("in service search return with return id");
		ResultSet resultset = null;
		boolean status = false;
		String query = "SELECT * FROM superrentdb.return_vehicle WHERE returnID = ?";
		try {
			sqlStatement = dbConnection.prepareStatement(query);
			sqlStatement.setInt(1, returnId);
			resultset = sqlStatement.executeQuery();
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
		}

		try {
			if(resultset == null || !resultset.next()){
				status = false;
			}else {
				//System.out.println(resultset);
				status = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		//System.out.println("in service : "+status);
		return status;
	}

	/**
	 * This method searches for the rent details of the given rent ID
	 * @PRE invoked from searchRentWithRentid method in ReturnLogic class
	 * @POST resultset with rent details are obtained
	 * @param rentId
	 * @return resultset
	 * @throws SQLException 
	 * 
	 */
	public ResultSet searchRentWithRentid(int rentId) throws SQLException {

		System.out.println("In searchRentWithRentid service, " + rentId);
		ResultSet resultset = null;
		String query = "SELECT * FROM superrentdb.rent WHERE rentID = ?";
		try {
			sqlStatement = dbConnection.prepareStatement(query);
			sqlStatement.setInt(1, rentId);
			resultset = sqlStatement.executeQuery();
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			throw sqlException;
		}
		return resultset;
	}

	/**
	 * This method searches for the rent details of the given vehicle ID 
	 * @PRE invoked from searchRentWithVehicleid function in ReturnLogic class
	 * @POST vehicle which is of status rented is stored in result set 
	 * @param rentId
	 * @return resultSet
	 * @throws SQLException 
	 */
	public ResultSet searchRentWithVehicleid(int vehicleID) throws SQLException {
		System.out.println("In searchRentWithVehicleid service, " + vehicleID);
		ResultSet resultset = null;
		String query = "SELECT * FROM superrentdb.rental_vehicle WHERE vehicleId = ? and status = 'rented' ";
		try {
			sqlStatement = dbConnection.prepareStatement(query);
			sqlStatement.setInt(1, vehicleID);
			resultset = sqlStatement.executeQuery();
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
			throw sqlException;
		}
		return resultset;
	}
	
	/**
	 * This method updates the status of Rented vehicle to available in rental_vehicle table once the return is successful  
	 * @PRE Invoked from updateRentedVehicle function in ReturnLogic class
	 * @POST Status of that vehicle is set to available 
	 * @param rentedVehicleID
	 * @return resultSet
	 * @throws SQLException
	 */
	public ResultSet updateRentedVehicle(int rentedVehicleID) throws SQLException{

		System.out.println("In update rented vehicle"+rentedVehicleID);
		ResultSet resultSet = null;
		String query = "UPDATE  rental_vehicle SET status = 'available' ,rentId = null , nextAvailableDate = null where vehicleId = ?;";
		try{
			dbConnection.setAutoCommit(false);
			sqlStatement = dbConnection.prepareStatement(query);
			sqlStatement.setInt(1,rentedVehicleID);
			sqlStatement.executeUpdate();
			dbConnection.commit();
		}catch (SQLException e1) {
			if(dbConnection != null){
				System.out.println("transaction is rolled back");
				try {
					dbConnection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		finally{
			try {
				dbConnection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}

		return resultSet;
	}

	/**
	 * This method saves the return values
	 * @PRE invoked from savereturn method in ReturnLogic class
	 * @POST saves the returned vehicle in the return_vehicle table
	 * @param returnEntity
	 * @return
	 * @throws SQLException 
	 */
	public void savereturn(ReturnEntity returnEntity) throws SQLException {		
		System.out.println("In service savereturn ");

		System.out.println("in save return : service "+returnEntity.getReturnId());
		//ResultSet resultSet = null;
		String query = "INSERT INTO superrentdb.return_vehicle values(?,?,?,?,?,?,?,?,?);";
		try{
			dbConnection.setAutoCommit(false);
			sqlStatement = dbConnection.prepareStatement(query);
			sqlStatement.setInt(1,returnEntity.getReturnId());
			sqlStatement.setString(2, returnEntity.getReturnDate().toString());
			//sqlStatement.setDate(2, new java.sql.Date(returnEntity.getReturnDate().getTime()));
			sqlStatement.setTime(3, Time.valueOf(returnEntity.getReturnTime()+":00"));
			sqlStatement.setInt(4, returnEntity.getReturnOdometerReading());
			sqlStatement.setString(5, returnEntity.getVehicleCondition());
			sqlStatement.setDouble(6, returnEntity.getFuelReading());
			sqlStatement.setBoolean(7, returnEntity.isRoadstar());
			sqlStatement.setInt(8,returnEntity.getCustmembershipId());
			sqlStatement.setInt(9,returnEntity.getPaymentId());
			sqlStatement.executeUpdate();

		}catch (SQLException e1) {
			if(dbConnection != null){
				System.out.println("transaction is rolled back");
				try {
					dbConnection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}

	}

	/**
	 * This method saves the return costs in return_cost table for the particular payment
	 * @PRE invoked from savereturncosts method in ReturnLogic class
	 * @POST  returnCosts are saved for that payment
	 * @param returnEntity
	 * @throws SQLException
	 */
	
	public void savereturncosts(ReturnEntity returnEntity) throws SQLException {		
		System.out.println("in service savereturncosts ");

		System.out.println("in save return costs : service "+returnEntity.getReturnId());
		//ResultSet resultSet = null;
		String query = "INSERT INTO superrentdb.return_cost values(?,?,?,?,?,?,?,?,?,?,?,?);";
		try{
			dbConnection.setAutoCommit(false);
			sqlStatement = dbConnection.prepareStatement(query);
			sqlStatement.setInt(1,returnEntity.getPaymentId());
			sqlStatement.setDouble(2,returnEntity.getAdditionalEquipmentCost());
			sqlStatement.setDouble(3,returnEntity.getRentalCost());
			sqlStatement.setDouble(4,returnEntity.getInsCost());
			sqlStatement.setDouble(5,returnEntity.getTax());
			sqlStatement.setDouble(6,returnEntity.getFine());
			sqlStatement.setDouble(7, returnEntity.getDamageCost());
			sqlStatement.setDouble(8, returnEntity.getMiscCharges());
			sqlStatement.setString(9, returnEntity.getMiscDesc());
			sqlStatement.setDouble(10,returnEntity.getDedInsCost());
			sqlStatement.setDouble(11, returnEntity.getTotalCost());
			sqlStatement.setDouble(12, returnEntity.getFinalCost());
			sqlStatement.executeUpdate();

		}catch (SQLException e1) {
			if(dbConnection != null){
				System.out.println("transaction is rolled back");
				try {
					dbConnection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}

	}

	public void transactionCommit() throws SQLException{
		System.out.println("in transaction commit");
		if(dbConnection!=null){
			try {
				dbConnection.commit();
			} catch (SQLException e) {
				if(dbConnection != null){
					System.out.println("transaction is rolled back");
					try {
						dbConnection.rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
						throw e1;
					}
				}
				e.printStackTrace();
				throw e;
			}
			finally{
				try {
					dbConnection.setAutoCommit(true);
				}
				catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
		}
	}

	public void transactionRollBack() throws SQLException{

		try {
			if(dbConnection != null){
				dbConnection.rollback();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}

		finally{
			try {
				dbConnection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
	}

	/**
	 * Closes an active database connection
	 * 
	 */
	
	public void closeConnection() {
		try {
			if (sqlStatement != null) {
				sqlStatement.close();
			}
			if (dbConnection != null) {
				dbConnection.close();
			}
		} catch (SQLException sqlExec) {
			sqlExec.printStackTrace();
		}
	}

}
